今更ながらBigQueryでERROR関数を試してみる

Google Cloudデータエンジニアのはんざわです。
先日、追加されたデータをデバックしたいケースがあったので、その方法を紹介したいと思います。

ERROR関数とは

ERROR関数は結果が評価されるとエラーで終了し、評価されなかった場合は正常終了します。
また、メッセージを指定することでエラー発生時にそのメッセージを表示させることが可能です。

• 使い方

ERROR(error_message)

• 例）

SELECT *
FROM (SELECT 1 AS x)
WHERE x >= 0 AND ERROR('xが0以上です');

> 'xが0以上です'

実際に触ってみる

1. 検証用のテーブルを作成する

まずは下記クエリで検証用のテーブルを作成します。

CREATE OR REPLACE TABLE `sample.assert_test`
(
  id INT64,
  name STRIN
);

INSERT INTO `sample.assert_test`
(
  id,
  name
)
VALUES
  (1, 'apple'),
  (2, 'banana');

2. 不正なデータを追加し、ERROR関数でエラーを発生させる

今回のケースではidのカラムをユニークキーと仮定し、重複したレコードがテーブルに追加された際にERROR関数でエラーを発生させます。

BEGIN

INSERT INTO `sample.assert_test`
(
  id,
  name
)
VALUES
  (1, 'apple');

SELECT ERROR('重複したデータが追加されました')
FROM
  sample.assert_test 
GROUP BY id
HAVING COUNT(id) > 1;

END;

想定通りエラーが発生しました。
ですが、このままでは重複の検知はできますが、データ自体は追加されてしまうのでトランザクションを導入し、重複データが追加されないようにします。

3. トランザクションを追加し、エラー発生時にロールバックさせる

BEGIN TRANSACTIONとCOMMIT TRANSACTIONを追加し、エラー発生時にロールバックするようにしました。

BEGIN

  BEGIN TRANSACTION;

  INSERT INTO `sample.assert_test`
  (
    id,
    name
  )
  VALUES
    (1, 'apple');

  SELECT ERROR('重複したデータが追加されました')
  FROM
    sample.assert_test 
  GROUP BY 1 
  HAVING COUNT(id) > 1;

  COMMIT TRANSACTION;

END;

エラー発生時にロールバックし、テーブルがデータ追加前の状態に戻っています。

おまけ

この記事ではERROR関数を紹介しました。
デバック関数には他にもASSERT構文もありますのでそちらも確認してみてください。
今回のようなデータのユニーク判定を行う場合はERROR関数を使った方が簡単にクエリを書くことができると思います。ASSERT構文でも実装は可能ですが、今回は簡単に書けるERROR関数を採用しました。

ERROR関数とASSERT構文の使い分け等は以下の記事でまとめられていたので是非参考にしてみてください。